﻿Imports DTO
Imports System.Data.OleDb
Public Class LoaiPhongDAO
    Public Shared Function LayDonGia(ByVal MaLoaiPhong As Integer) As Long
        Dim dongia As Long
        Dim sql As String = "select DonGia from LOAIPHONG where MaLoaiPhong=" + MaLoaiPhong.ToString()
        Dim rd As String = Dataprovider.ExecuteScalar(sql)
        dongia = Long.Parse(rd)
        Return dongia
    End Function
    Public Shared Function LayDanhSach() As List(Of LoaiPhong)
        Dim sql As String = "select * from LOAIPHONG"
        Dim rd As OleDbDataReader = Dataprovider.ExecuteReader(sql)
        Dim list As New List(Of LoaiPhong)
        While (rd.Read())
            Dim lp As New LoaiPhong
            lp.MaLoaiPhong = rd.GetInt32(0)
            lp.TenLoaiPhong = rd.GetString(1)
            lp.DonGia = rd.GetInt32(2)
            list.Add(lp)
        End While
        Return list
    End Function
    Public Shared Function LayTenLoaiPhong(ByVal MaLoaiPhong As Integer) As String
        Dim sql As String = "select TenLoaiPhong from LOAIPHONG where MaLoaiPhong=" + MaLoaiPhong.ToString()
        Return Dataprovider.ExecuteScalar(sql)
    End Function
    Public Shared Function TimKiem(ByVal lp As LoaiPhongCRT) As List(Of LoaiPhong)
        Dim ds As New List(Of LoaiPhong)
        Dim strtenphong As String = "1=1"
        Dim strloaiphong As String = "1=1"
        Dim strgia As String = "1=1"
        If lp.CheckDonGia = True Then
            strgia = "DonGia between " + lp.DonGiaTu.ToString() + " and " + lp.DonGiaDen.ToString()
        End If
        If lp.CheckMaLoaiPhong = True Then
            strloaiphong = "MaLoaiPhong=" + lp.MaLoaiPhong.ToString()
        End If
        If lp.CheckTenLoaiPhong = True Then
            strtenphong = "TenLoaiPhong like" + lp.TenLoaiPhong
        End If
        Dim sql As String = "select * from LOAIPHONG where " + strtenphong + " and " + strloaiphong + " and " + strgia
        Dim rd As OleDbDataReader = Dataprovider.ExecuteReader(sql)
        While (rd.Read())
            Dim lphong As New LoaiPhong
            lphong.MaLoaiPhong = rd.GetInt32(0)
            lphong.TenLoaiPhong = rd.GetString(1)
            lphong.DonGia = rd.GetInt32(2)
            ds.Add(lphong)
        End While
        Return ds
    End Function
    Public Shared Function DoanhThuTheoMa(ByVal maloaiphong As Integer, ByVal ngay As Date) As Long
        Try
            Dim ngaydauthang As New Date(ngay.Year, ngay.Month, 1)
            Dim ngaycuoithang As New Date(ngay.Year, ngay.Month, 31)
            Dim sql As String = "select sum(hd.TriGia) from HOADON hd,CHITIETHOADON cthd,PHONG p,LOAIPHONG lp where hd.MaHoaDon=cthd.MaHoaDon and cthd.MaPhong=p.MaPhong and p.MaLoaiPhong=lp.MaLoaiPhong and lp.MaLoaiPhong=" + maloaiphong.ToString() + "and hd.NgayThanhToan between #" + ngaydauthang + "# and #" + ngaycuoithang + "#"
            Return Long.Parse(Dataprovider.ExecuteScalar(sql))
        Catch ex As Exception
            Return 0
        End Try
       
    End Function
    Public Shared Function DoanhThu(ByVal ngay As Date) As Long
        Try
            Dim ngaydauthang As New Date(ngay.Year, ngay.Month, 1)
            Dim ngaycuoithang As New Date(ngay.Year, ngay.Month, 31)
            Dim sql As String = "select sum(hd.TriGia) from HOADON hd,CHITIETHOADON cthd,PHONG p,LOAIPHONG lp where hd.MaHoaDon=cthd.MaHoaDon and cthd.MaPhong=p.MaPhong and p.MaLoaiPhong=lp.MaLoaiPhong and hd.NgayThanhToan between #" + ngaydauthang + "# and #" + ngaycuoithang + "#"

            Return Long.Parse(Dataprovider.ExecuteScalar(sql))
        Catch ex As Exception
            Return 0
        End Try
       
    End Function
End Class
